package cn.udrm.elasticsearch.fileread;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;

/**
 * 读取Excel文件内容
 * @author xurongbei
 */
public class ExcelReadContent {

    public static String getXlsContent(File file) throws IOException {
        StringBuffer stringBuffer=new StringBuffer();
        FileInputStream fis=new FileInputStream(file);
        Workbook wb = new HSSFWorkbook(fis);//由输入流文件得到工作簿对象
        int sheetSize=wb.getNumberOfSheets();
        for(int s=0;s<sheetSize;s++){
            Sheet sheet = wb.getSheetAt(s);//获取第一个sheet
            if(sheet==null){
                continue;
            }
            int lastRowNum = sheet.getLastRowNum(); //获取表格内容的最后一行的行数
            //rowBegin代表要开始读取的行号，下面这个循环的作用是读取每一行内容
            for (int i = 0; i <= lastRowNum; ++i) {
                Row row = sheet.getRow(i);//获取每一行
                if(row==null){
                    continue;
                }
                int columnNum = row.getLastCellNum();//获取每一行的最后一列的列号，即总列数
                for (int j=0; j<columnNum; ++j) {
                    Cell cell = row.getCell(j);//获取每个单元格
                   if(cell==null){
                       continue;
                   }
                    if(cell.getCellTypeEnum()== CellType.NUMERIC){
                        stringBuffer.append(cell.getNumericCellValue());
                    }else if(cell.getCellTypeEnum()==CellType.STRING){
                       stringBuffer.append(cell.getStringCellValue());
                    }else if(cell.getCellTypeEnum()==CellType.FORMULA){
                        stringBuffer.append(cell.getCellFormula());
                    }else if(cell.getCellTypeEnum()==CellType.BLANK){

                    }else if(cell.getCellTypeEnum()==CellType.BOOLEAN){
                        stringBuffer.append(cell.getBooleanCellValue());
                    }else if(cell.getCellTypeEnum()==CellType.ERROR){

                    }
                    stringBuffer.append(" ");
                }
            }
        }
        fis.close();
        return stringBuffer.toString();
    }


    public static String getXlsxContent(File file) throws Exception {
        StringBuffer stringBuffer=new StringBuffer();
        ExcelReader reader = new ExcelReader() {
            public void getRows(int sheetIndex, int curRow, List<String> rowList) {
                if(rowList.size()>0){
                    stringBuffer.append(StringUtils.join(rowList,","));
                }

            }
        };
        reader.process(file.getPath());
        return stringBuffer.toString();
    }
}
